package com.pioneer.util;

import com.pioneer.dao.*;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;

import java.util.Properties;
import java.util.List;
import java.util.ArrayList;
import java.util.StringTokenizer;
import java.io.*;

/**
 * Title:        Pioneer Application
 * Description:
 * Copyright:    Copyright (c) 2001
 * Company:      Pioneer Metals
 * @author Gary Chen
 * @version 1.0
 */

public class ItemImporter  {

	public static final String DATABASE_DRIVER="database_driver";
	public static final String DATABASE_URL="database_url";
	public static final String DATABASE_USER="database_user";
	public static final String DATABASE_PASSWORD="database_password";	

	protected Connection connection;

    public ItemImporter(Properties properties)
	throws DataAccessException
	{
		try
		{
			Class.forName(properties.getProperty(DATABASE_DRIVER));
		}
		catch (ClassNotFoundException ex)
		{
			throw new DataAccessException(ex.getMessage());
		}
		try
		{
		    connection=DriverManager.getConnection(properties.getProperty(DATABASE_URL),
		    properties.getProperty(DATABASE_USER),properties.getProperty(DATABASE_PASSWORD));
                    connection.setAutoCommit(false);
		}
		catch (SQLException ex)
		{
			throw new DataAccessException(ex.getMessage());
		}
    }

	public void destroy()
	{
		try
		{
		    connection.close();
		}
		catch (SQLException ex)
		{
			System.err.println(ex.getMessage());
		}
	}
	 
   /**
   * This function clears out any single quotes, replacing them with
   * sql-safe escape charactered single quotes (i.e. '-> '')
   */
	
  public static String sqlSafe(String sql){
	if (sql.startsWith("\"") && (sql.endsWith("\""))) {
		sql = sql.substring(1,sql.length()-1);
	}
	sql = sql.replaceAll("'","''");
	return sql;
  }
	
	private void importFile(FileInputStream fos)
	throws DataAccessException,IOException{
		BufferedReader reader = new BufferedReader(new InputStreamReader(fos));
		String newLine = null;
		Statement stat=null;
		String sqlString=null;
		String[] columns = null;
		try
		{
			stat=connection.createStatement();
			System.out.println("Deleting all previous items...");
			sqlString= "DELETE FROM item";
			System.out.println(stat.executeUpdate(sqlString)+" rows affected by delete by "+sqlString);
			System.out.println("Iimporting new items...");
			reader.readLine();// read header
			while ((newLine = reader.readLine())!=null) {
				//System.out.println(newLine);
				int column = 0;
				columns = newLine.split("\t");
				if (columns.length<4) {
					System.err.println("Error importing at line after "+sqlString+".  There were only "+columns.length+" columns found.");
				}
				else {
					sqlString= "INSERT INTO item (item_num,description,cost,price) VALUES ('"+
					    sqlSafe(columns[0])+"','"+sqlSafe(columns[1])+"',"+columns[2]+","+columns[3]+")";
					System.out.println(stat.executeUpdate(sqlString)+" rows affected by insert by "+sqlString);
				}
			}
                        sqlString= "INSERT INTO item (item_num,description,"+
                        "cost,price) VALUES ('--',' ',0,0)";
                        System.out.println(stat.executeUpdate(sqlString)+
                        " rows affected by insert by "+sqlString);
                        connection.commit();
		}
		catch (SQLException ex)
		{
			System.err.println("Database error: "+ex.getMessage()+" using command "+sqlString+". Attempting rollback.");
                        try{ 
                           connection.rollback();
                        }
                        catch(SQLException rollex){
                           System.err.println("Rollback failed: "+rollex.getMessage());
                        }
		}
		finally
		{
			try
			{
				if (stat!=null) stat.close();
				fos.close();
			}
			catch (SQLException ex)
			{
				System.err.println(ex.getMessage());
			}
		}

	}
	
	public static void main(String args[]) {
		try {
			if (args.length<1) {
				System.out.println("Please enter the name of the tab-delimited file to import.");
				System.out.println("Use file format: <item_num>(tab)<description>(tab)<cost>(tab)<price>");
				System.exit(1);
			}
			AppProperties.init();
			String userDir = AppProperties.getProperty(AppProperties.USERDIR);
			String stdErr = AppProperties.getProperty(AppProperties.ERRORFILE);
			String stdOut = AppProperties.getProperty(AppProperties.LOGFILE);
			//System.out.println("Refer to "+userDir+File.separator+stdErr+" for any possible errors and "+userDir+File.separator+stdOut+" to view items successfully imported.");
			System.setErr(new PrintStream(new FileOutputStream(userDir+File.separator+stdErr)));
			System.setOut(new PrintStream(new FileOutputStream(userDir+File.separator+stdOut)));
			
			//Properties props = new Properties();
			//props.load(new FileInputStream(FileConstant.CONFIG_PATH+java.io.File.separator+FileConstant.DBPROP));
			ItemImporter importer = new ItemImporter(AppProperties.getProperties());
			importer.importFile(new FileInputStream(args[0]));
		}
		catch(Exception ex) {
			ex.printStackTrace();	
		}
	}
	
}
